﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class UserPhotoDAL
    {
        private const string Waming = "SqlServerDAL.UserPhotoDAL";

        public bool Save(UserPhotoDto dto)
        {
            try
            {
                string sql;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@PhotoName", SqlDbType.VarChar) {Value = dto.PhotoName},
                                         new SqlParameter("@PhotoAnnexId", SqlDbType.Int) {Value = dto.PhotoAnnexId},
                                         new SqlParameter("@PhotoAlbumId", SqlDbType.Int) {Value = dto.PhotoAlbumId},
                                         new SqlParameter("@PhotoUserId", SqlDbType.Int) {Value = dto.PhotoUserId},
                                         new SqlParameter("@PhotoTime", SqlDbType.DateTime) {Value = dto.PhotoTime},
                                         new SqlParameter("@PhotoDesc", SqlDbType.VarChar) {Value = dto.PhotoDesc}
                                     };
                if (dto.PhotoId == 0)
                {
                    sql =
                        @"insert into UserPhoto(PhotoName,PhotoAnnexId,PhotoAlbumId,PhotoUserId,PhotoTime,PhotoDesc) values(@PhotoName,
                        @PhotoAnnexId,@PhotoAlbumId,@PhotoUserId,@PhotoTime,@PhotoDesc)";
                }
                else
                {
                    sql =
                        @"update UserPhoto set PhotoName=@PhotoName,PhotoAnnexId=@PhotoAnnexId,PhotoAlbumId=@PhotoAlbumId,
                        PhotoUserId=@PhotoUserId,PhotoTime=@PhotoTime,PhotoDesc=@PhotoDesc where PhotoId=@PhotoId";
                    parameters.Add(new SqlParameter("@PhotoDesc", SqlDbType.VarChar) { Value = dto.PhotoDesc });
                }
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(UserPhotoDto dto)", ex);
                return false;
            }
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, int userId, int albumId, string order)
        {
            try
            {
                order = string.IsNullOrEmpty(order) ? "order by PhotoId desc" : order;
                var hts = new List<Hashtable>();
                var sql = "select row_number() over (" + order + ") as rowNumber, * from UserPhoto";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (userId != 0)
                {
                    whereSql = " where PhotoUserId=@PhotoUserId";
                    parameters.Add(new SqlParameter("@PhotoUserId", SqlDbType.Int) { Value = userId });
                }
                if (albumId != 0)
                {
                    whereSql += whereSql == "" ? " where PhotoAlbumId=@PhotoAlbumId" : " and PhotoAlbumId=@PhotoAlbumId";
                    parameters.Add(new SqlParameter("@PhotoAlbumId", SqlDbType.Int) { Value = albumId });
                }
                sql = string.Format(
                    "select * from (" + sql + whereSql + ") as UserPhoto where rowNumber between {0} AND {1}",
                    firstResult, maxResult);
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SlicedFindAll(int firstResult, int maxResult, int userId, int albumId, string order)", ex);
                return null;
            }
        }

        public int SelectCount(int userId, int albumId)
        {
            try
            {
                const string sql = "select count(*) from UserPhoto";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (userId != 0)
                {
                    whereSql = " where PhotoUserId=@PhotoUserId";
                    parameters.Add(new SqlParameter("@PhotoUserId", SqlDbType.Int) { Value = userId });
                }
                if (albumId != 0)
                {
                    whereSql += whereSql == "" ? " where PhotoAlbumId=@PhotoAlbumId" : " and PhotoAlbumId=@PhotoAlbumId";
                    parameters.Add(new SqlParameter("@PhotoAlbumId", SqlDbType.Int) { Value = albumId });
                }
                var red = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql + whereSql,
                                                  parameters.ToArray());
                return red != DBNull.Value ? Convert.ToInt32(red) : -1;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectCount(int userId, int albumId)", ex);
                return -1;
            }
        }

        public List<Hashtable> FindAll(int userId, int albumId, string order)
        {
            try
            {
                var hts = new List<Hashtable>();
                order = string.IsNullOrEmpty(order) ? "order by PhotoId desc" : order;
                const string sql = "select * from UserPhoto";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (userId != 0)
                {
                    whereSql = " where PhotoUserId=@PhotoUserId";
                    parameters.Add(new SqlParameter("@PhotoUserId", SqlDbType.Int) { Value = userId });
                }
                if (albumId != 0)
                {
                    whereSql += whereSql == "" ? " where PhotoAlbumId=@PhotoAlbumId" : " and PhotoAlbumId=@PhotoAlbumId";
                    parameters.Add(new SqlParameter("@PhotoAlbumId", SqlDbType.Int) { Value = albumId });
                }
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql + whereSql + " " + order, parameters.ToArray()))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".FindAll(int userId, int albumId, string order)", ex);
                return null;
            }
        }
    }
}
